﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[RootQueueActivation]
AS
BEGIN
	DECLARE @messages TABLE(ConversationHandle uniqueidentifier, RecvMessage xml, RecvMessageName sysname)
	
	WHILE (1=1)
	BEGIN

	BEGIN TRANSACTION;

	WAITFOR
	( RECEIVE conversation_handle
			 ,message_body
			 ,message_type_name
      FROM [RootQueue]
	  INTO @messages
	), TIMEOUT 5000;

	IF (@@ROWCOUNT = 0)
	BEGIN
	  ROLLBACK TRANSACTION;
	  BREAK;
	END

	DECLARE @RecvMessage xml
	DECLARE @RecvMessageName sysname
	DECLARE @DialogHandle uniqueidentifier
	DECLARE @RecvReqDlgHandle uniqueidentifier
	DECLARE @SubscriptionId bigint
	DECLARE @IsOverflowService bit
	
	DECLARE msgCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
	FOR 
		SELECT [ConversationHandle], [RecvMessage], [RecvMessageName], [DialogHandle], [SubscriptionId], CONVERT(bit, CASE WHEN LEFT([Target], 2) = 'O_' THEN 1 ELSE 0 END) AS IsOverflowService
		FROM @messages
		JOIN [ServiceMetadata] ON [Source] = 'RootService'
		LEFT JOIN [Subscription] ON [EventName] = [RecvMessage].value('declare namespace x="urn:www-vecozo-com:types:events:v2"; (/x:GenericEvent/x:Name)[1]', 'nvarchar(128)') AND [ServiceName] = [Target]
		
	OPEN msgCursor
	
	FETCH NEXT FROM msgCursor INTO 
		@RecvReqDlgHandle, @RecvMessage, @RecvMessageName, @DialogHandle, @SubscriptionId, @IsOverflowService
	
	WHILE @@FETCH_STATUS = 0
	BEGIN

	
		IF @RecvMessageName =
			N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
		BEGIN
		   END CONVERSATION @RecvReqDlgHandle;
		END
		ELSE IF @RecvMessageName =
			N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
		BEGIN
		   END CONVERSATION @RecvReqDlgHandle;
		END
		ELSE IF (@SubscriptionId IS NOT NULL OR @IsOverflowService = 1)
		
			SEND ON CONVERSATION @DialogHandle (@RecvMessage);
		
		
		FETCH NEXT FROM msgCursor INTO 
			@RecvReqDlgHandle, @RecvMessage, @RecvMessageName, @DialogHandle, @SubscriptionId, @IsOverflowService
	END
	
	CLOSE msgCursor
	
	DELETE @messages;

	COMMIT TRANSACTION;

	END

END
